﻿using Furion;
using Furion.Logging.Extensions;
using Microsoft.AspNetCore.DataProtection.KeyManagement;
using Microsoft.Data.SqlClient;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace DataSync.Core
{
    public class DataTableHelper
    {
        /// <summary>
        /// 获取所有的数据表
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public static List<string> GetAllTables(SqlConnection connection)
        {
            List<string> tableNames = new List<string>();

            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_CATALOG=DB_NAME() ORDER BY TABLE_NAME ASC";

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tableNames.Add((string)reader["TABLE_NAME"]);
                    }
                }
            }

            return tableNames;
        }

        /// <summary>
        /// 获取datatable
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="scope"></param>
        /// <returns></returns>
        public static DataTable FetchDataFromTable(string tableName, SqlSugarScope scope)
        {
            var sql = $"SELECT * FROM {tableName}";
            DataTable dataTable = scope.Ado.GetDataTable(sql);
            return dataTable;
        }
        /// <summary>
        /// 获取表主键字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="connectionstring"></param>
        /// <returns></returns>
        public static List<string> GetPrimaryKeyFieldName(string tableName, SqlConnection connectionstring)
        {
            var req = new List<string>();
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connectionstring;
                command.CommandText = $"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{tableName}' AND CONSTRAINT_NAME LIKE 'PK%'";

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        req.Add((string)reader["COLUMN_NAME"]);
                    }
                }
            }
            return req;
        }
        /// <summary>
        /// 复制主键到目标表
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="targetTable"></param>
        /// <param name="sourceConn"></param>
        /// <param name="targetConn"></param>
        public static void CopyPrimaryKeyToTargetTable(string sourceTable, string targetTable, SqlConnection sourceConn, SqlConnection targetConn)
        {
            var copyQuery = $@"EXEC sp_helpconstraint '{sourceTable}'";

            using (var cmd = new SqlCommand(copyQuery, sourceConn))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if ((int)reader["ConstraintType"] == 3 && (bool)reader["IsPrimaryKey"])
                        {
                            var constraintName = (string)reader["name"];
                            var alterQuery = $"ALTER TABLE {targetTable}" +
                                             $" ADD CONSTRAINT [{constraintName}] FOREIGN KEY ([Id]) REFERENCES {sourceTable}([Id]);";

                            using (var alterCmd = new SqlCommand(alterQuery, targetConn))
                            {
                                alterCmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 根据传来的数据行，自动生成UPDATA的SQL语句，更新条件由程序自动产生
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKeyName">主键</param>
        /// <param name="Identities"></param>
        /// <param name="dataRow">数据行</param>
        /// <returns></returns>
        public static string ConstructUpdateSql(string tableName, List<string> primaryKeyName, List<string> Identities, DataRow dataRow)
        {
            string sql = "UPDATE " + tableName + " SET ";
            string whereStr = "WHERE ";
            if (primaryKeyName != null && primaryKeyName.Count > 0)
            {
                //存在多个主键。取自增列做筛选条件
                if (primaryKeyName.Count > 1 && Identities != null && Identities.Count > 0)
                {
                    foreach (var item in Identities)
                    {
                        whereStr = whereStr + item + @" = '" + dataRow[item].ToString() + @"' and ";
                    }
                }
                else
                {
                    foreach (var item in primaryKeyName)
                    {
                        whereStr = whereStr + item + @" = '" + dataRow[item].ToString() + @"' and ";
                    }

                }
            }
            foreach (DataColumn cloumn in dataRow.Table.Columns)
            {
                if (primaryKeyName.Count > 1 && Identities != null && Identities.Count > 0)
                {
                    if (Identities.Contains(cloumn.ColumnName))
                    {
                        continue;//跳过自增列
                    }
                }
                else
                {
                    if (primaryKeyName.Contains(cloumn.ColumnName))
                    {
                        continue;//跳过自增列
                    }
                }

                switch (cloumn.DataType.Name.ToUpper())
                {
                    case "STRING":
                        sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
                        break;

                    case "INT":
                    case "INT16":
                    case "INT32":
                    case "INT64":
                    case "DECIMAL":
                        sql = sql + cloumn + @" = " + dataRow[cloumn].ToString() + @" ,";
                        break;
                    case "DATE":
                    case "TIME":
                    case "DATETIME":
                        sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
                        break;

                    case "BOOLEAN":

                        sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
                        break;

                    default:
                        ("未处理类型：" + cloumn.DataType.Name.ToUpper()).LogError();
                        break;
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            whereStr = whereStr.Substring(0, whereStr.Length - 4);
            sql = sql + whereStr;

            return sql;
        }
        /// <summary>
        /// 生成Update语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataRow"></param>
        /// <returns></returns>
        public static string ConstructUpdateSql(string tableName, DataRow dataRow)
        {
            string sql = "UPDATE " + tableName + " SET ";
            string whereStr = "WHERE ";
            //if (!string.IsNullOrEmpty(primaryKeyName))
            //{
            //    whereStr = whereStr + primaryKeyName + @" = '" + dataRow[primaryKeyName].ToString() + @"' and ";
            //}
            //foreach (DataColumn cloumn in dataRow.Table.Columns)
            //{
            //    if (cloumn.ColumnName == primaryKeyName)
            //    {
            //        continue;//跳过主键
            //    }
            //    switch (cloumn.DataType.Name.ToUpper())
            //    {
            //        case "STRING":
            //            sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
            //            break;

            //        case "INT":
            //        case "INT16":
            //        case "INT32":
            //        case "INT64":
            //        case "DECIMAL":
            //            sql = sql + cloumn + @" = " + dataRow[cloumn].ToString() + @" ,";
            //            break;
            //        case "DATE":
            //        case "TIME":
            //        case "DATETIME":
            //            sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
            //            break;

            //        case "BOOLEAN":

            //            sql = sql + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' ,";
            //            break;

            //        default:
            //            ("未处理类型：" + cloumn.DataType.Name.ToUpper()).LogError();
            //            break;
            //    }
            //}
            sql = sql.Substring(0, sql.Length - 1);
            whereStr = whereStr.Substring(0, whereStr.Length - 4);
            sql = sql + whereStr;

            return sql;

        }
        /// <summary>
        /// 生成Delete语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataRow"></param>
        /// <returns></returns>
        public static string ConstructDeleteSql(string tableName, DataRow dataRow)
        {
            // "DELETE FROM table_name WHERE condition;";
            string sql = "DELETE FROM" + tableName;
            string whereStr = " WHERE "; ;
            foreach (DataColumn cloumn in dataRow.Table.Columns)
            {
                switch (cloumn.DataType.Name.ToUpper())
                {
                    case "STRING":
                        whereStr = whereStr + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' and ";
                        break;

                    case "INT":
                    case "INT16":
                    case "INT32":
                    case "INT64":
                    case "DECIMAL":
                        whereStr = whereStr + cloumn + @" = " + dataRow[cloumn].ToString() + @" and ";
                        break;
                    case "DATE":
                    case "TIME":
                    case "DATETIME":
                        whereStr = whereStr + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' and ";
                        break;

                    case "BOOLEAN":
                        switch (dataRow[cloumn].ToString())
                        {
                            case "True":
                                whereStr = whereStr + @"(" + cloumn + @" =  '1' OR " + cloumn + @" = 'true' OR " + cloumn + @" = 'True') and ";
                                break;
                            case "False":
                                whereStr = whereStr + @"(" + cloumn + @" =  '0' OR " + cloumn + @" = 'false' OR " + cloumn + @" = 'False') and ";
                                break;
                            default:
                                break;
                        }

                        break;

                    default:
                        ("未处理类型：" + cloumn.DataType.Name.ToUpper()).LogError();
                        break;
                }
            }
            whereStr = whereStr.Substring(0, whereStr.Length - 4);
            sql = sql + whereStr;
            return sql;

        }

        public static string ConstructDeleteSql(string tableName, List<string> primaryKeyName, List<string> Identities, DataRow dataRow)
        {
            // "DELETE FROM table_name WHERE condition;";
            string sql = "DELETE FROM " + tableName;
            string whereStr = " WHERE ";
            foreach (DataColumn cloumn in dataRow.Table.Columns)
            {
                if (primaryKeyName.Count > 1 && Identities != null && Identities.Count > 0)
                {
                    if (!Identities.Contains(cloumn.ColumnName))
                    {
                        continue;//跳过主键
                    }

                }
                else
                {
                    if (!primaryKeyName.Contains(cloumn.ColumnName))
                    {
                        continue;//跳过主键
                    }
                }

                switch (cloumn.DataType.Name.ToUpper())
                {
                    case "STRING":
                        whereStr = whereStr + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' and ";
                        break;

                    case "INT":
                    case "INT16":
                    case "INT32":
                    case "INT64":
                    case "DECIMAL":
                        whereStr = whereStr + cloumn + @" = " + dataRow[cloumn].ToString() + @" and ";
                        break;
                    case "DATE":
                    case "TIME":
                    case "DATETIME":
                        whereStr = whereStr + cloumn + @" = '" + dataRow[cloumn].ToString() + @"' and ";
                        break;

                    case "BOOLEAN":
                        switch (dataRow[cloumn].ToString())
                        {
                            case "True":
                                whereStr = whereStr + @"(" + cloumn + @" =  '1' OR " + cloumn + @" = 'true' OR " + cloumn + @" = 'True') and ";
                                break;
                            case "False":
                                whereStr = whereStr + @"(" + cloumn + @" =  '0' OR " + cloumn + @" = 'false' OR " + cloumn + @" = 'False') and ";
                                break;
                            default:
                                break;
                        }

                        break;

                    default:
                        ("未处理类型：" + cloumn.DataType.Name.ToUpper()).LogError();
                        break;
                }
            }
            whereStr = whereStr.Substring(0, whereStr.Length - 4);
            sql = sql + whereStr;
            return sql;

        }

        /// <summary>
        /// 生成insert语句
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataRow"></param>
        /// <returns></returns>
        public static string ConstructInsertSql(string tableName, DataRow dataRow)
        {
            // "INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);";
            string sql = "INSERT  INTO " + tableName + " ( AppName,Version,AppType,CreateDate) VALUES( ";
            string endSql = ") ";
            foreach (DataColumn cloumn in dataRow.Table.Columns)
            {
                switch (cloumn.DataType.Name.ToUpper())
                {
                    case "STRING":
                        sql = sql + "'" + dataRow[cloumn].ToString() + "' ,";
                        break;

                    case "INT":
                    case "INT16":
                    case "INT32":
                    case "INT64":
                    case "DECIMAL":
                        sql = sql + dataRow[cloumn].ToString() + " ,";
                        break;
                    case "DATE":
                    case "TIME":
                    case "DATETIME":
                        sql = sql + "'" + dataRow[cloumn].ToString() + "' ,";
                        break;

                    case "BOOLEAN":

                        sql = sql + "'" + dataRow[cloumn].ToString() + "' ,";
                        break;

                    default:
                        ("未处理类型：" + cloumn.DataType.Name.ToUpper()).LogError();
                        break;
                }
            }

            sql = sql.Substring(0, sql.Length - 1);
            sql = sql + endSql;

            return sql;
        }
    }
}